package com.wmx.controller;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel 文件控制层
 *
 * @author wangMaoXiong
 * @version 1.0
 * @date 2021/6/9 20:18
 */
@RestController
@SuppressWarnings("all")
public class ExcelController {

    /**
     * 演示下载 excel 文件
     * http://localhost:8080/excel/exportDataDrafts
     *
     * @param response
     */
    @RequestMapping(value = {"excel/exportDataDrafts"}, method = {RequestMethod.GET})
    public void exportDataDrafts(HttpServletResponse response) {
        String fileName = "蚩尤后裔_" + System.currentTimeMillis();
        OutputStream outputStream = null;
        Workbook workbook = null;
        try {
            // 设置 excel 下载头信息，filename 必须进行编码，否则下载时中文回乱码
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename=" + (URLEncoder.encode(fileName, "UTF-8")) + ".xlsx");

            outputStream = response.getOutputStream();
            workbook = this.createWorkbook();
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 创建工作簿
     *
     * @param workbook
     */
    public Workbook createWorkbook() {
        List<String> headList = Arrays.asList("编号", "型号", "生产日期", "指导价", "数量(台)", "经办人");
        List<List<Object>> contentData = this.contentData();

        //1）创建工作簿、创建纸张、设置纸张名称
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(0, "华为手机采购表");

        //设置每列的宽度（列宽）
        for (int i = 0; i < headList.size(); i++) {
            sheet.setColumnWidth(i, 16 * 256);
        }

        //2）表头字体
        Font headFont = workbook.createFont();
        headFont.setFontHeightInPoints((short) 13);//设置字体高度
        headFont.setFontName("宋体");
        //HSSFColorPredefined 是一个枚举，其中提供了常用的颜色
        headFont.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        headFont.setBold(true);//设置字体加粗，默认字体为 Arial

        //3）正文字体
        Font contentFont = workbook.createFont();
        contentFont.setFontHeightInPoints((short) 11);//设置字体高度
        contentFont.setFontName("宋体");
        contentFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());

        //4）表头样式
        CellStyle headCellStyle = workbook.createCellStyle();
        //设置填充模式。org.apache.poi.ss.usermodel.FillPatternType：单元格格式的填充图案样式的枚举值
        headCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//SOLID_FOREGROUND：实心填充
        headCellStyle.setBorderBottom(BorderStyle.THICK);//设置下边框为粗线
        //设置背景色填充颜色
        headCellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
        headCellStyle.setAlignment(HorizontalAlignment.CENTER);//设置表头内容水平居中显示，默认左对齐
        headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);////设置表头内容垂直居中显示，默认下对齐
        headCellStyle.setFont(headFont);//为样式设置字体

        //5）正文样式
        CellStyle contentCellStyle = workbook.createCellStyle();
        contentCellStyle.setBorderBottom(BorderStyle.THIN);//设置下边框为细线
        contentCellStyle.setFont(contentFont);

        //6）创建表头
        Row row = sheet.createRow(0);//创建表头行，行号从0开始
        Cell cell;//单元格对象
        for (int i = 0; i < headList.size(); ++i) {
            cell = row.createCell(i);//创建第一行的列（单元格），列号从0开始
            cell.setCellStyle(headCellStyle);
            cell.setCellValue(headList.get(i));
        }

        //7）创建正文内容
        for (int i = 0; i < contentData.size(); i++) {
            List<Object> rowList = contentData.get(i);
            row = sheet.createRow(i + 1);
            for (int j = 0; j < rowList.size(); j++) {
                cell = row.createCell(j);
                cell.setCellStyle(contentCellStyle);
                Object cellValue = rowList.get(j);
                cellValue = cellValue == null ? "" : cellValue;
                cell.setCellValue(cellValue.toString());
            }
        }
        return workbook;
    }

    /**
     * 生成表格测试内容
     *
     * @return
     */
    private List<List<Object>> contentData() {
        List<List<Object>> contentList = new ArrayList<>(8);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        for (int i = 0; i < 30; i++) {
            List<Object> rowList = new ArrayList<>();
            rowList.add(i + 1);
            rowList.add("华为P4" + i);
            rowList.add(dateFormat.format(new Date()));
            rowList.add(56700);
            rowList.add(new Random().nextInt(1000));
            rowList.add("张三");
            contentList.add(rowList);
        }
        return contentList;
    }
}
